﻿//=======================================================================================
// Microsoft Windows Server AppFabric Customer Advisory Team (CAT) Best Practices Series
//
// This sample is supplemental to the technical guidance published on the community
// blog at http://blogs.msdn.com/appfabriccat/. 
//
//=======================================================================================
// Copyright © 2010 Microsoft Corporation. All rights reserved.
// 
// THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER 
// EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF 
// MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. YOU BEAR THE RISK OF USING IT.
//=======================================================================================

using System;
using System.Data.SqlClient;
using System.Data;
using System.Xml;

namespace HashFoo.SqlServer.TransientDbConnection
{

    #region Using references
    #endregion

    /// <summary>
    /// Provides a reliable way of opening connections to and executing commands against the SQL Azure 
    /// databases taking into account potential network unreliability and a requirement for connection retry.
    /// </summary>
    public sealed class ReliableSqlConnection : IDbConnection, ICloneable
    {
        #region Private members
        private readonly RetryPolicy _commandRetryPolicy;
        private readonly RetryPolicy _connectionRetryPolicy;
        private readonly SqlConnection _underlyingConnection;
        #endregion

        #region Constructors
        /// <summary>
        /// Initializes a new instance of the SqlAzureConnection class with a given connection string. Uses the default
        /// retry policy for connections and commands unless retry settings are provided in the connection string.
        /// </summary>
        /// <param name="connectionString">The connection string used to open the SQL Azure database.</param>
        public ReliableSqlConnection(string connectionString)
            : this(connectionString, RetryPolicyFactory.GetDefaultSqlConnectionRetryPolicy()) {}

        /// <summary>
        /// Initializes a new instance of the SqlAzureConnection class with a given connection string
        /// and a policy defining whether to retry a request if the connection fails to be opened or a command
        /// fails to be successfully executed.
        /// </summary>
        /// <param name="connectionString">The connection string used to open the SQL Azure database.</param>
        /// <param name="retryPolicy">The retry policy defining whether to retry a request if a connection or a command fails.</param>
        public ReliableSqlConnection(string connectionString, RetryPolicy retryPolicy)
            : this(connectionString, retryPolicy, ChooseBetween(RetryPolicyFactory.GetDefaultSqlCommandRetryPolicy(), retryPolicy)) {}

        /// <summary>
        /// Initializes a new instance of the SqlAzureConnection class with a given connection string
        /// and a policy defining whether to retry a request if the connection fails to be opened or a command
        /// fails to be successfully executed.
        /// </summary>
        /// <param name="connectionString">The connection string used to open the SQL Azure database.</param>
        /// <param name="connectionRetryPolicy">The retry policy defining whether to retry a request if a connection fails to be established.</param>
        /// <param name="commandRetryPolicy">The retry policy defining whether to retry a request if a command fails to be executed.</param>
        public ReliableSqlConnection(string connectionString, RetryPolicy connectionRetryPolicy, RetryPolicy commandRetryPolicy)
        {
            _underlyingConnection = new SqlConnection(connectionString);
            _connectionRetryPolicy = connectionRetryPolicy;
            _commandRetryPolicy = commandRetryPolicy;
        }
        #endregion

        #region Public properties
        /// <summary>
        /// Specified the policy which decides whether to retry a connection request, based on how many 
        /// times the request has been made and the reason for the last failure. 
        /// </summary>
        public RetryPolicy ConnectionRetryPolicy
        {
            get { return _connectionRetryPolicy; }
        }

        /// <summary>
        /// Specified the policy which decides whether to retry a command, based on how many 
        /// times the request has been made and the reason for the last failure. 
        /// </summary>
        public RetryPolicy CommandRetryPolicy
        {
            get { return _commandRetryPolicy; }
        }

        /// <summary>
        /// An instance of the SqlConnection object that represents the connection to an instance of SQL Azure database.
        /// </summary>
        public SqlConnection Current
        {
            get { return _underlyingConnection; }
        }

        /// <summary>
        /// Returns the CONTEXT_INFO value that was set for the current session. This value can be used for tracing the query execution problems. 
        /// </summary>
        public Guid SessionTracingId
        {
            get
            {
                try
                {
                    using (var query = SqlCommandFactory.CreateGetContextInfoCommand(Current))
                    {
                        // Execute the query in retry-aware fashion, retry if necessary.
                        return ExecuteCommand<Guid>(query);
                    }
                }
                catch
                {
                    // Any failure will result in returning a default Guid value. This is by design.
                    return Guid.Empty;
                }
            }
        }

        /// <summary>
        /// Gets or sets the connection string for opening a connection to the SQL Azure database.
        /// </summary>
        public string ConnectionString
        {
            get { return _underlyingConnection.ConnectionString; }
            set { _underlyingConnection.ConnectionString = value; }
        }
        #endregion

        #region Public methods
        /// <summary>
        /// Opens a database connection with the settings specified by the ConnectionString and ConnectionRetryPolicy properties.
        /// </summary>
        /// <returns>An object representing the open connection.</returns>
        public SqlConnection Open()
        {
            return Open(ConnectionRetryPolicy);
        }

        /// <summary>
        /// Opens a database connection with the settings specified by the ConnectionString and specified retry policy.
        /// </summary>
        /// <param name="retryPolicy">The retry policy defining whether to retry a request if the connection fails to be opened.</param>
        /// <returns>An object representing the open connection.</returns>
        public SqlConnection Open(RetryPolicy retryPolicy)
        {
            // Check if retry policy was specified, if not, disable retries by executing the Open method using RetryPolicy.NoRetry.
            (retryPolicy ?? RetryPolicy.NoRetry).ExecuteAction(
                () =>
                {
                    if (_underlyingConnection.State != ConnectionState.Open) _underlyingConnection.Open();
                });

            return _underlyingConnection;
        }

        /// <summary>
        /// Executes a SQL command and returns a result defined by the specified type T. This method uses the retry policy specified when 
        /// instantiating the SqlAzureConnection class (or the default retry policy if no policy was set at construction time).
        /// </summary>
        /// <typeparam name="T">Either IDataReader, XmlReader or any .NET type defining the type of result to be returned.</typeparam>
        /// <param name="command">A SqlCommand object containing the SQL command to be executed.</param>
        /// <returns>An instance of an IDataReader, XmlReader or any .NET object containing the result.</returns>
        public T ExecuteCommand<T>(IDbCommand command)
        {
            return ExecuteCommand<T>(command, CommandRetryPolicy, CommandBehavior.Default);
        }

        /// <summary>
        /// Executes a SQL command and returns a result defined by the specified type T. This method uses the retry policy specified when 
        /// instantiating the SqlAzureConnection class (or the default retry policy if no policy was set at construction time).
        /// </summary>
        /// <typeparam name="T">Either IDataReader, XmlReader or any .NET type defining the type of result to be returned.</typeparam>
        /// <param name="command">A SqlCommand object containing the SQL command to be executed.</param>
        /// <param name="behavior">Provides a description of the results of the query and its effect on the database.</param>
        /// <returns>An instance of an IDataReader, XmlReader or any .NET object containing the result.</returns>
        public T ExecuteCommand<T>(IDbCommand command, CommandBehavior behavior)
        {
            return ExecuteCommand<T>(command, CommandRetryPolicy, behavior);
        }

        /// <summary>
        /// Executes a SQL command using the specified retry policy and returns a result defined by the specified type T.
        /// </summary>
        /// <typeparam name="T">Either IDataReader, XmlReader or any .NET type defining the type of result to be returned.</typeparam>
        /// <param name="command">A SqlCommand object containing the SQL command to be executed.</param>
        /// <param name="retryPolicy">The retry policy defining whether to retry a command if a connection fails while executing the command.</param>
        /// <returns>An instance of an IDataReader, XmlReader or any .NET object containing the result.</returns>
        public T ExecuteCommand<T>(IDbCommand command, RetryPolicy retryPolicy)
        {
            return ExecuteCommand<T>(command, retryPolicy, CommandBehavior.Default);
        }

        /// <summary>
        /// Executes a SQL command using the specified retry policy and returns a result defined by the specified type T.
        /// </summary>
        /// <typeparam name="T">Either IDataReader, XmlReader or any .NET type defining the type of result to be returned.</typeparam>
        /// <param name="command">A SqlCommand object containing the SQL command to be executed.</param>
        /// <param name="retryPolicy">The retry policy defining whether to retry a command if a connection fails while executing the command.</param>
        /// <param name="behavior">Provides a description of the results of the query and its effect on the database.</param>
        /// <returns>An instance of an IDataReader, XmlReader or any .NET object containing the result.</returns>
        public T ExecuteCommand<T>(IDbCommand command, RetryPolicy retryPolicy, CommandBehavior behavior)
        {
            return 
                (retryPolicy ?? RetryPolicy.NoRetry).ExecuteAction(
                    () =>
                    {
                        // Make sure the command has been associated with a valid connection. If not, associate it with an opened SQL connection.
                        if (command.Connection == null)
                        {
                            // Open a new connection and assign it to the command object.
                            command.Connection = Open();
                        }

                        // Verify whether or not the connection is valid and is open. This code may be retried therefore
                        // it is important to ensure that a connection is re-established should it have previously failed.
                        if (command.Connection.State != ConnectionState.Open) command.Connection.Open();

                        var resultType = typeof(T);

                        if (resultType.IsAssignableFrom(typeof(IDataReader))) return (T)command.ExecuteReader(behavior);
                        else if (resultType.IsAssignableFrom(typeof(XmlReader)))
                        {
                            if (command is SqlCommand)
                            {
                                object result = null;
                                var xmlReader = (command as SqlCommand).ExecuteXmlReader();

                                if ((behavior & CommandBehavior.CloseConnection) == CommandBehavior.CloseConnection)
                                {
                                    // Implicit conversion from XmlReader to <T> via an intermediary object.
                                    result = new SqlXmlReader(command.Connection, xmlReader);
                                }
                                else
                                {
                                    // Implicit conversion from XmlReader to <T> via an intermediary object.
                                    result = xmlReader;
                                }

                                return (T)result;
                            }
                            else throw new NotSupportedException();
                        }
                        else if (resultType == typeof(NonQueryResult))
                        {
                            var result = new NonQueryResult();
                            result.RecordsAffected = command.ExecuteNonQuery();

                            return (T)Convert.ChangeType(result, resultType);
                        }
                        else
                        {
                            var result = command.ExecuteScalar();

                            if (result != null) return (T)Convert.ChangeType(result, resultType);
                            else return default(T);
                        }
                    });
        }

        /// <summary>
        /// Executes a SQL command and returns the number of rows affected.
        /// </summary>
        /// <param name="command">A SqlCommand object containing the SQL command to be executed.</param>
        /// <returns>The number of rows affected.</returns>
        public int ExecuteCommand(IDbCommand command)
        {
            return ExecuteCommand(command, CommandRetryPolicy);
        }

        /// <summary>
        /// Executes a SQL command and returns the number of rows affected.
        /// </summary>
        /// <param name="command">A SqlCommand object containing the SQL command to be executed.</param>
        /// <param name="retryPolicy">The retry policy defining whether to retry a command if a connection fails while executing the command.</param>
        /// <returns>The number of rows affected.</returns>
        public int ExecuteCommand(IDbCommand command, RetryPolicy retryPolicy)
        {
            var result = ExecuteCommand<NonQueryResult>(command, retryPolicy);

            return result.RecordsAffected;
        }
        #endregion

        #region IDbConnection implementation
        /// <summary>
        /// Begins a database transaction with the specified System.Data.IsolationLevel value.
        /// </summary>
        /// <param name="level">One of the System.Data.IsolationLevel values.</param>
        /// <returns>An object representing the new transaction.</returns>
        public IDbTransaction BeginTransaction(IsolationLevel level)
        {
            return _underlyingConnection.BeginTransaction(level);
        }

        /// <summary>
        /// Begins a database transaction.
        /// </summary>
        /// <returns>An object representing the new transaction.</returns>
        public IDbTransaction BeginTransaction()
        {
            return _underlyingConnection.BeginTransaction();
        }

        /// <summary>
        /// Changes the current database for an open Connection object.
        /// </summary>
        /// <param name="databaseName">The name of the database to use in place of the current database.</param>
        public void ChangeDatabase(string databaseName)
        {
            _underlyingConnection.ChangeDatabase(databaseName);
        }

        /// <summary>
        /// Opens a database connection with the settings specified by the ConnectionString
        /// property of the provider-specific Connection object.
        /// </summary>
        void IDbConnection.Open()
        {
            Open();
        }

        /// <summary>
        /// Closes the connection to the database.
        /// </summary>
        public void Close()
        {
            _underlyingConnection.Close();
        }

        /// <summary>
        /// Gets the time to wait while trying to establish a connection before terminating
        /// the attempt and generating an error.
        /// </summary>
        public int ConnectionTimeout
        {
            get { return _underlyingConnection.ConnectionTimeout; }
        }

        /// <summary>
        /// Creates and returns an object implementing the IDbCommand interface which is associated 
        /// with the underlying SqlConnection.
        /// </summary>
        /// <returns>A System.Data.SqlClient.SqlCommand object.</returns>
        IDbCommand IDbConnection.CreateCommand()
        {
            return _underlyingConnection.CreateCommand();
        }

        /// <summary>
        /// Gets the name of the current database or the database to be used after a
        /// connection is opened.
        /// </summary>
        public string Database
        {
            get { return _underlyingConnection.Database; }
        }

        /// <summary>
        /// Gets the current state of the connection.
        /// </summary>
        public ConnectionState State
        {
            get { return _underlyingConnection.State; }
        }

        /// <summary>
        /// Creates and returns a SqlCommand object associated with the underlying SqlConnection.
        /// </summary>
        /// <returns>A System.Data.SqlClient.SqlCommand object.</returns>
        public SqlCommand CreateCommand()
        {
            return _underlyingConnection.CreateCommand();
        }
        #endregion

        #region ICloneable implementation
        /// <summary>
        /// Creates a new connection that is a copy of the current instance including the connection
        /// string, connection retry policy and command retry policy.
        /// </summary>
        /// <returns></returns>
        object ICloneable.Clone()
        {
            return new ReliableSqlConnection(ConnectionString, ConnectionRetryPolicy, CommandRetryPolicy);
        }
        #endregion

        #region IDisposable implementation
        /// <summary>
        /// Performs application-defined tasks associated with freeing, releasing, or
        /// resetting managed and unmanaged resources.
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        /// <summary>
        /// Performs application-defined tasks associated with freeing, releasing, or
        /// resetting managed and unmanaged resources.
        /// </summary>
        /// <param name="disposing">A flag indicating that managed resources must be released.</param>
        private void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (_underlyingConnection.State == ConnectionState.Open) _underlyingConnection.Close();

                _underlyingConnection.Dispose();
            }
        }
        #endregion

        #region Private helper classes
        /// <summary>
        /// Allows to choose the retry policy from a selection of the main and default one depending on whether or not the main policy is specified.
        /// </summary>
        /// <param name="mainPolicy">The main policy to be chosen if not null.</param>
        /// <param name="defaultPolicy">The default policy to be chosen as an alternative.</param>
        /// <returns>The chosen policy object.</returns>
        private static RetryPolicy ChooseBetween(RetryPolicy mainPolicy, RetryPolicy defaultPolicy)
        {
            return mainPolicy ?? defaultPolicy;
        }

        /// <summary>
        /// This helpers class is intended to be used exclusively for fetching the number of affected records when executing a command using ExecuteNonQuery.
        /// </summary>
        private sealed class NonQueryResult
        {
            public int RecordsAffected { get; set; }
        }
        #endregion
    }
}